package backend

import (
	"database/sql"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"time"

	_ "modernc.org/sqlite"
)

// WitsDbClient WITS数据DAO工具类
type WitsDbClient struct {
	db         *sql.DB
	dataDir    string
	mu         sync.RWMutex
	partitions map[string]*sql.DB // 分区数据库缓存
}

// NewWitsDbClient 创建新的WITS数据库客户端
func NewWitsDbClient(dataDir string) (*WitsDbClient, error) {
	if err := os.MkdirAll(dataDir, 0755); err != nil {
		return nil, fmt.Errorf("failed to create data directory: %v", err)
	}

	client := &WitsDbClient{
		dataDir:    dataDir,
		partitions: make(map[string]*sql.DB),
	}

	return client, nil
}

// Close 关闭所有数据库连接
func (c *WitsDbClient) Close() error {
	c.mu.Lock()
	defer c.mu.Unlock()

	var errs []error
	for _, db := range c.partitions {
		if err := db.Close(); err != nil {
			errs = append(errs, err)
		}
	}

	if len(errs) > 0 {
		return fmt.Errorf("errors closing databases: %v", errs)
	}
	return nil
}

// getPartitionDB 获取指定月份的分区数据库
func (c *WitsDbClient) getPartitionDB(partitionKey string) (*sql.DB, error) {
	c.mu.RLock()
	if db, exists := c.partitions[partitionKey]; exists {
		c.mu.RUnlock()
		return db, nil
	}
	c.mu.RUnlock()

	c.mu.Lock()
	defer c.mu.Unlock()

	// 双重检查
	if db, exists := c.partitions[partitionKey]; exists {
		return db, nil
	}

	// 创建新的分区数据库
	dbPath := filepath.Join(c.dataDir, fmt.Sprintf("wits_%s.db", partitionKey))
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open partition database %s: %v", partitionKey, err)
	}

	// 设置连接池参数
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(24 * time.Hour)

	// 创建表结构
	if err := c.createPartitionTable(db); err != nil {
		db.Close()
		return nil, fmt.Errorf("failed to create partition table: %v", err)
	}

	c.partitions[partitionKey] = db
	return db, nil
}

// createPartitionTable 创建分区表结构
func (c *WitsDbClient) createPartitionTable(db *sql.DB) error {
	createTableSQL := `
	CREATE TABLE IF NOT EXISTS wits_data (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		timestamp DATETIME NOT NULL,
		well_id TEXT NOT NULL,
		rig_id TEXT,
		record_type INTEGER,
		hole_depth REAL,
		bit_depth REAL,
		measured_depth REAL,
		rop REAL,
		wob REAL,
		rpm REAL,
		torque REAL,
		hook_load REAL,
		hook_position REAL,
		hook_velocity REAL,
		block_height REAL,
		standpipe_pressure REAL,
		casing_pressure REAL,
		pump1_pressure REAL,
		pump2_pressure REAL,
		pump3_pressure REAL,
		pump1_spm REAL,
		pump2_spm REAL,
		pump3_spm REAL,
		total_spm REAL,
		flow_rate_in REAL,
		flow_rate_out REAL,
		flow_rate_percent REAL,
		mud_weight_in REAL,
		mud_weight_out REAL,
		mud_temp_in REAL,
		mud_temp_out REAL,
		mud_conductivity REAL,
		mud_volume REAL,
		shaker1 INTEGER,
		shaker2 INTEGER,
		shaker3 INTEGER,
		desander INTEGER,
		desilter INTEGER,
		total_gas REAL,
		methane REAL,
		ethane REAL,
		propane REAL,
		butane REAL,
		pentane REAL,
		h2s REAL,
		co2 REAL,
		caliper REAL,
		tvd REAL,
		inclination REAL,
		azimuth REAL,
		dogleg_severity REAL,
		drawworks_status INTEGER,
		rotary_status INTEGER,
		pumps_status INTEGER,
		alarm_code INTEGER,
		alarm_level INTEGER,
		alarm_message TEXT,
		data_quality INTEGER,
		data_source TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_wits_timestamp ON wits_data(timestamp);
	CREATE INDEX IF NOT EXISTS idx_wits_well_id ON wits_data(well_id);
	CREATE INDEX IF NOT EXISTS idx_wits_record_type ON wits_data(record_type);
	CREATE INDEX IF NOT EXISTS idx_wits_timestamp_well ON wits_data(timestamp, well_id);
	`

	_, err := db.Exec(createTableSQL)
	return err
}

// getPartitionKey 根据时间获取分区键 (格式: YYYYMM)
func (c *WitsDbClient) getPartitionKey(t time.Time) string {
	return t.Format("200601")
}

// getPartitionKeysByTimeRange 获取时间范围内的所有分区键
func (c *WitsDbClient) getPartitionKeysByTimeRange(startTime, endTime time.Time) []string {
	partitionKeys := make(map[string]bool)

	current := time.Date(startTime.Year(), startTime.Month(), 1, 0, 0, 0, 0, startTime.Location())
	end := time.Date(endTime.Year(), endTime.Month(), 1, 0, 0, 0, 0, endTime.Location())

	for !current.After(end) {
		partitionKeys[c.getPartitionKey(current)] = true
		current = current.AddDate(0, 1, 0) // 下个月
	}

	keys := make([]string, 0, len(partitionKeys))
	for key := range partitionKeys {
		keys = append(keys, key)
	}

	return keys
}

// InsertWitsData 插入WITS数据
func (c *WitsDbClient) InsertWitsData(data *WitsData) error {
	partitionKey := c.getPartitionKey(data.Timestamp)
	db, err := c.getPartitionDB(partitionKey)
	if err != nil {
		return err
	}

	query := `
	INSERT INTO wits_data (
		timestamp, well_id, rig_id, record_type,
		hole_depth, bit_depth, measured_depth,
		rpm, wob, torque,
		hook_load, hook_position, hook_velocity, block_height,
		standpipe_pressure, casing_pressure, pump1_pressure, pump2_pressure, pump3_pressure,
		pump1_spm, pump2_spm, pump3_spm, total_spm,
		flow_rate_in, flow_rate_out, flow_rate_percent,
		mud_weight_in, mud_weight_out, mud_temp_in, mud_temp_out, mud_conductivity, mud_volume,
		shaker1, shaker2, shaker3, desander, desilter,
		total_gas, methane, ethane, propane, butane, pentane, h2s, co2,
		caliper, tvd, inclination, azimuth, dogleg_severity,
		drawworks_status, rotary_status, pumps_status,
		alarm_code, alarm_level, alarm_message,
		data_quality, data_source
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`

	_, err = db.Exec(query,
		data.Timestamp, data.WellID, data.RigID, data.RecordType,
		data.HoleDepth, data.BitDepth, data.MeasuredDepth,
		data.ROP, data.WOB, data.RPM, data.Torque,
		data.HookLoad, data.HookPosition, data.HookVelocity, data.BlockHeight,
		data.StandpipePressure, data.CasingPressure, data.Pump1Pressure, data.Pump2Pressure, data.Pump3Pressure,
		data.Pump1SPM, data.Pump2SPM, data.Pump3SPM, data.TotalSPM,
		data.FlowRateIn, data.FlowRateOut, data.FlowRatePercent,
		data.MudWeightIn, data.MudWeightOut, data.MudTempIn, data.MudTempOut, data.MudConductivity, data.MudVolume,
		boolToInt(data.Shaker1), boolToInt(data.Shaker2), boolToInt(data.Shaker3), boolToInt(data.Desander), boolToInt(data.Desilter),
		data.TotalGas, data.Methane, data.Ethane, data.Propane, data.Butane, data.Pentane, data.H2S, data.CO2,
		data.Caliper, data.TVD, data.Inclination, data.Azimuth, data.DoglegSeverity,
		data.DrawworksStatus, data.RotaryStatus, data.PumpsStatus,
		data.AlarmCode, data.AlarmLevel, data.AlarmMessage,
		data.DataQuality, data.DataSource,
	)

	if err != nil {
		return fmt.Errorf("failed to insert wits data: %v", err)
	}

	return nil
}

// QueryByTimeRange 按时间范围查询数据
func (c *WitsDbClient) QueryByTimeRange(startTime, endTime time.Time) ([]*WitsData, error) {
	partitionKeys := c.getPartitionKeysByTimeRange(startTime, endTime)

	var allData []*WitsData
	for _, partitionKey := range partitionKeys {
		data, err := c.queryPartitionByTimeRange(partitionKey, startTime, endTime)
		if err != nil {
			log.Printf("Error querying partition %s: %v", partitionKey, err)
			continue // 继续处理其他分区
		}
		allData = append(allData, data...)
	}

	return allData, nil
}

// QueryByWellAndTimeRange 按井号和时间范围查询数据
func (c *WitsDbClient) QueryByWellAndTimeRange(wellID string, startTime, endTime time.Time) ([]*WitsData, error) {
	partitionKeys := c.getPartitionKeysByTimeRange(startTime, endTime)

	var allData []*WitsData
	for _, partitionKey := range partitionKeys {
		data, err := c.queryPartitionByWellAndTimeRange(partitionKey, wellID, startTime, endTime)
		if err != nil {
			log.Printf("Error querying partition %s for well %s: %v", partitionKey, wellID, err)
			continue
		}
		allData = append(allData, data...)
	}

	return allData, nil
}

// QueryByPartition 按分区查询数据
func (c *WitsDbClient) QueryByPartition(partitionKey string, limit int) ([]*WitsData, error) {
	db, err := c.getPartitionDB(partitionKey)
	if err != nil {
		return nil, err
	}

	query := `
	SELECT timestamp, well_id, rig_id, record_type,
		hole_depth, bit_depth, measured_depth, rop,
		rpm, wob, torque,
		hook_load, hook_position, hook_velocity, block_height,
		standpipe_pressure, casing_pressure, pump1_pressure, pump2_pressure, pump3_pressure,
		pump1_spm, pump2_spm, pump3_spm, total_spm,
		flow_rate_in, flow_rate_out, flow_rate_percent,
		mud_weight_in, mud_weight_out, mud_temp_in, mud_temp_out, mud_conductivity, mud_volume,
		shaker1, shaker2, shaker3, desander, desilter,
		total_gas, methane, ethane, propane, butane, pentane, h2s, co2,
		caliper, tvd, inclination, azimuth, dogleg_severity,
		drawworks_status, rotary_status, pumps_status,
		alarm_code, alarm_level, alarm_message,
		data_quality, data_source
	FROM wits_data
	ORDER BY timestamp DESC
	LIMIT ?
	`

	rows, err := db.Query(query, limit)
	if err != nil {
		return nil, fmt.Errorf("failed to query partition %s: %v", partitionKey, err)
	}
	defer rows.Close()

	return c.scanWitsDataRows(rows)
}

// queryPartitionByTimeRange 查询指定分区的时间范围数据
func (c *WitsDbClient) queryPartitionByTimeRange(partitionKey string, startTime, endTime time.Time) ([]*WitsData, error) {
	db, err := c.getPartitionDB(partitionKey)
	if err != nil {
		return nil, err
	}

	query := `
	SELECT timestamp, well_id, rig_id, record_type,
		hole_depth, bit_depth, measured_depth, rop,
		rpm, wob, torque,
		hook_load, hook_position, hook_velocity, block_height,
		standpipe_pressure, casing_pressure, pump1_pressure, pump2_pressure, pump3_pressure,
		pump1_spm, pump2_spm, pump3_spm, total_spm,
		flow_rate_in, flow_rate_out, flow_rate_percent,
		mud_weight_in, mud_weight_out, mud_temp_in, mud_temp_out, mud_conductivity, mud_volume,
		shaker1, shaker2, shaker3, desander, desilter,
		total_gas, methane, ethane, propane, butane, pentane, h2s, co2,
		caliper, tvd, inclination, azimuth, dogleg_severity,
		drawworks_status, rotary_status, pumps_status,
		alarm_code, alarm_level, alarm_message,
		data_quality, data_source
	FROM wits_data
	WHERE timestamp >= ? AND timestamp <= ?
	ORDER BY timestamp ASC
	`

	rows, err := db.Query(query, startTime, endTime)
	if err != nil {
		return nil, fmt.Errorf("failed to query partition %s: %v", partitionKey, err)
	}
	defer rows.Close()

	return c.scanWitsDataRows(rows)
}

// queryPartitionByWellAndTimeRange 查询指定分区的井号和时间范围数据
func (c *WitsDbClient) queryPartitionByWellAndTimeRange(partitionKey, wellID string, startTime, endTime time.Time) ([]*WitsData, error) {
	db, err := c.getPartitionDB(partitionKey)
	if err != nil {
		return nil, err
	}

	query := `
	SELECT timestamp, well_id, rig_id, record_type,
		hole_depth, bit_depth, measured_depth,
		rpm, wob, torque,
		hook_load, hook_position, hook_velocity, block_height,
		standpipe_pressure, casing_pressure, pump1_pressure, pump2_pressure, pump3_pressure,
		pump1_spm, pump2_spm, pump3_spm, total_spm,
		flow_rate_in, flow_rate_out, flow_rate_percent,
		mud_weight_in, mud_weight_out, mud_temp_in, mud_temp_out, mud_conductivity, mud_volume,
		shaker1, shaker2, shaker3, desander, desilter,
		total_gas, methane, ethane, propane, butane, pentane, h2s, co2,
		caliper, tvd, inclination, azimuth, dogleg_severity,
		drawworks_status, rotary_status, pumps_status,
		alarm_code, alarm_level, alarm_message,
		data_quality, data_source
	FROM wits_data
	WHERE well_id = ? AND timestamp >= ? AND timestamp <= ?
	ORDER BY timestamp ASC
	`

	rows, err := db.Query(query, wellID, startTime, endTime)
	if err != nil {
		return nil, fmt.Errorf("failed to query partition %s for well %s: %v", partitionKey, wellID, err)
	}
	defer rows.Close()

	return c.scanWitsDataRows(rows)
}

// scanWitsDataRows 扫描查询结果并转换为WitsData结构体
func (c *WitsDbClient) scanWitsDataRows(rows *sql.Rows) ([]*WitsData, error) {
	var results []*WitsData

	for rows.Next() {
		var data WitsData
		var timestamp string
		var shaker1, shaker2, shaker3, desander, desilter int

		err := rows.Scan(
			&timestamp, &data.WellID, &data.RigID, &data.RecordType,
			&data.HoleDepth, &data.BitDepth, &data.MeasuredDepth,
			&data.ROP, &data.WOB, &data.RPM, &data.Torque,
			&data.HookLoad, &data.HookPosition, &data.HookVelocity, &data.BlockHeight,
			&data.StandpipePressure, &data.CasingPressure, &data.Pump1Pressure, &data.Pump2Pressure, &data.Pump3Pressure,
			&data.Pump1SPM, &data.Pump2SPM, &data.Pump3SPM, &data.TotalSPM,
			&data.FlowRateIn, &data.FlowRateOut, &data.FlowRatePercent,
			&data.MudWeightIn, &data.MudWeightOut, &data.MudTempIn, &data.MudTempOut, &data.MudConductivity, &data.MudVolume,
			&shaker1, &shaker2, &shaker3, &desander, &desilter,
			&data.TotalGas, &data.Methane, &data.Ethane, &data.Propane, &data.Butane, &data.Pentane, &data.H2S, &data.CO2,
			&data.Caliper, &data.TVD, &data.Inclination, &data.Azimuth, &data.DoglegSeverity,
			&data.DrawworksStatus, &data.RotaryStatus, &data.PumpsStatus,
			&data.AlarmCode, &data.AlarmLevel, &data.AlarmMessage,
			&data.DataQuality, &data.DataSource,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan row: %v", err)
		}

		// 解析时间戳
		data.Timestamp, err = time.Parse("2006-01-02 15:04:05", timestamp)
		if err != nil {
			log.Printf("Warning: failed to parse timestamp %s: %v", timestamp, err)
			continue
		}

		// 转换布尔值
		data.Shaker1 = intToBool(shaker1)
		data.Shaker2 = intToBool(shaker2)
		data.Shaker3 = intToBool(shaker3)
		data.Desander = intToBool(desander)
		data.Desilter = intToBool(desilter)

		results = append(results, &data)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating rows: %v", err)
	}

	return results, nil
}

// GetPartitions 获取所有分区键
func (c *WitsDbClient) GetPartitions() ([]string, error) {
	files, err := os.ReadDir(c.dataDir)
	if err != nil {
		return nil, fmt.Errorf("failed to read data directory: %v", err)
	}

	var partitions []string
	for _, file := range files {
		if file.IsDir() {
			continue
		}

		name := file.Name()
		if strings.HasPrefix(name, "wits_") && strings.HasSuffix(name, ".db") {
			// 提取分区键 (wits_YYYYMM.db -> YYYYMM)
			partition := strings.TrimPrefix(name, "wits_")
			partition = strings.TrimSuffix(partition, ".db")
			if len(partition) == 6 {
				partitions = append(partitions, partition)
			}
		}
	}

	return partitions, nil
}

// GetPartitionStats 获取分区统计信息
func (c *WitsDbClient) GetPartitionStats(partitionKey string) (map[string]interface{}, error) {
	db, err := c.getPartitionDB(partitionKey)
	if err != nil {
		return nil, err
	}

	stats := make(map[string]interface{})

	// 获取记录数
	var count int
	err = db.QueryRow("SELECT COUNT(*) FROM wits_data").Scan(&count)
	if err != nil {
		return nil, fmt.Errorf("failed to get count: %v", err)
	}
	stats["record_count"] = count

	// 获取时间范围
	var minTime, maxTime string
	err = db.QueryRow("SELECT MIN(timestamp), MAX(timestamp) FROM wits_data").Scan(&minTime, &maxTime)
	if err != nil {
		return nil, fmt.Errorf("failed to get time range: %v", err)
	}
	stats["min_time"] = minTime
	stats["max_time"] = maxTime

	// 获取井号列表
	wells := []string{}
	rows, err := db.Query("SELECT DISTINCT well_id FROM wits_data ORDER BY well_id")
	if err != nil {
		return nil, fmt.Errorf("failed to get well list: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var well string
		if err := rows.Scan(&well); err == nil {
			wells = append(wells, well)
		}
	}
	stats["wells"] = wells

	return stats, nil
}

// boolToInt 布尔值转整数
func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

// intToBool 整数转布尔值
func intToBool(i int) bool {
	return i != 0
}
